CREATE DATABASE ProjectManagement

GO
USE ProjectManagement
CREATE TABLE Users
(
	username nvarchar(50) NOT NULL PRIMARY KEY,
	password nvarchar(50) NOT NULL,
	fullName nvarchar(50),
	userRole nvarchar(50)
)
GO

CREATE TABLE Projects
(
	ProjectID int NOT NULL PRIMARY KEY IDENTITY(1,1),
	ProjectName nvarchar(50),
	ProjectManager nvarchar(50) NOT NULL FOREIGN KEY REFERENCES Users(username),
	Description nvarchar(1000),
	NumOfMinestone int,
	StartDay date,
	EndDay date,
	Status nvarchar(50)
	)
GO

CREATE TABLE Minestones
(
	MinestoneID int NOT NULL PRIMARY KEY IDENTITY(1,1),
	MinestoneName nvarchar(50),
	ProjectID int NOT NULL FOREIGN KEY REFERENCES Projects(ProjectID),
	Description nvarchar(1000),
	StartDay date,
	EndDay date,
	Status nvarchar(50)	
)
GO

CREATE TABLE Members
(
	MemberID int NOT NULL PRIMARY KEY IDENTITY(1,1),
	ProjectID int NOT NULL FOREIGN KEY REFERENCES Projects(ProjectID),
	username nvarchar(50) NOT NULL FOREIGN KEY REFERENCES Users(username),
	Status nvarchar(50)	
)
GO

CREATE TABLE Tasks
(
	TaskID int NOT NULL PRIMARY KEY IDENTITY(1,1),
	TaskName nvarchar(50),
	MinestoneID int NOT NULL FOREIGN KEY REFERENCES Minestones(MinestoneID),
	username nvarchar(50) NOT NULL FOREIGN KEY REFERENCES Users(username),
	Description nvarchar(1000),
	StartDay date,
	EndDay date,
	Status nvarchar(50)
)
GO